import pandas as pd
from pathlib import Path
from datetime import datetime

# 配置常量
DATA_DIR = Path("data")
OUTPUT_DIR = DATA_DIR / "alldata"  # 输出目录

# 输入文件
TRAVEL_DETAILS_FILE = DATA_DIR / "差旅报销行程明细(商旅).xlsx"
TRANSPORT_EXPENSE_FILE = DATA_DIR / "差旅费城市间交通费.xlsx"
TRAVEL_ALLOWANCE_FILE = DATA_DIR / "差旅费出差补助.xlsx"

# 输出文件
OUTPUT_FILE = OUTPUT_DIR / "超天数报销交通补助分析结果(培训申请).xlsx"

def clean_column_names(df):
    """
    清理DataFrame的列名，去除多余的空格
    """
    return df.rename(columns=lambda x: x.strip())

def load_data():
    """
    加载Excel文件的数据并进行基础处理
    
    返回值：
        tuple: 包含DataFrame的元组
    """
    # 确保输出目录存在
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    
    try:
        # 读取Excel文件
        travel_details = pd.read_excel(TRAVEL_DETAILS_FILE,dtype='object')
        transport_expenses = pd.read_excel(TRANSPORT_EXPENSE_FILE)
        travel_allowance = pd.read_excel(TRAVEL_ALLOWANCE_FILE)  # 添加读取差旅费出差补助表
        
        # 清理列名中的空格
        travel_details = clean_column_names(travel_details)
        transport_expenses = clean_column_names(transport_expenses)
        travel_allowance = clean_column_names(travel_allowance)
        
        # 打印列名，帮助调试
        print("\n第一个表的列名：")
        print(travel_details.columns.tolist())
        print("\n第二个表的列名：")
        print(transport_expenses.columns.tolist())
        print("\n第三个表的列名：")
        print(travel_allowance.columns.tolist())
        
        # 将日期时间列转换为统一格式
        datetime_columns = ['出发时间', '到达时间']
        
        for df in [travel_details, transport_expenses]:
            for col in datetime_columns:
                if col in df.columns:
                    df[col] = pd.to_datetime(df[col], errors='coerce')
        
        # 将分录编号和辅助编号转换为4位字符串格式
        for df in [travel_details, transport_expenses]:
            if '分录编号' in df.columns:
                df['分录编号'] = df['分录编号'].astype(str).str.zfill(4)
            if '辅助编号' in df.columns:
                df['辅助编号'] = df['辅助编号'].astype(str).str.zfill(4)
        
        return travel_details, transport_expenses, travel_allowance
    
    except Exception as e:
        print(f"加载数据时出错: {str(e)}")
        raise

def analyze_travel_data(travel_details, transport_expenses, travel_allowance):
    """
    分析差旅数据
    """
    try:
        # 重命名第二个表的列名以匹配第一个表
        column_rename = {
            '员工编号': '人员编号',
            '起始地名称': '出发地名称',
            '交通工具': '交通工具编号'
        }
        transport_expenses = transport_expenses.rename(columns=column_rename)
        
        # 重命名差旅费出差补助表的列名
        allowance_rename = {
            '员工': '人员编号'
        }
        travel_allowance = travel_allowance.rename(columns=allowance_rename)
        
        # 从差旅费出差补助表获取领取补助天数，只获取补助类型为交通补助的记录
        reimbursement_days = travel_allowance[
            (travel_allowance['补助类型名称'] == '交通补助')
        ][['单据编号', '人员编号', '天数']].copy()
        
        # 按单据编号和人员编号分组，计算天数总和
        reimbursement_days = reimbursement_days.groupby(['单据编号', '人员编号'])['天数'].sum().reset_index()
        reimbursement_days = reimbursement_days.rename(columns={'天数': '领取交通补助总天数'})
        
        # 合并数据
        merged_data = pd.merge(
            travel_details,
            transport_expenses[['单据编号', '人员编号', '出发地名称', '出发时间', '到达时间', '交通工具编号']],
            on=['单据编号', '人员编号', '出发地名称', '出发时间'],
            how='left',
            suffixes=('', '_transport')
        )
        
        # 合并领取补助天数
        merged_data = pd.merge(
            merged_data,
            reimbursement_days,
            on=['单据编号', '人员编号'],
            how='left'
        )
        
        # 处理到达时间
        if '到达时间' in merged_data.columns:
            # 先用第二个表的到达时间填充
            merged_data['到达时间'] = merged_data['到达时间_transport']
            # 对于仍然为空的值，使用第二个表的出发时间
            mask = merged_data['到达时间'].isna()
            merged_data.loc[mask, '到达时间'] = merged_data.loc[mask, '出发时间']
        
        # 按单据编号和人员编号分组，并按出发时间和辅助编号排序
        merged_data.sort_values(
            by=['单据编号', '人员编号', '出发时间', '辅助编号'],
            ascending=[True, True, True, True],
            inplace=True
        )
        
        # 计算出差天数
        def calculate_travel_days(group):
            # 初始化出差天数列和常住地列
            group['出差天数'] = None
            group['常住地'] = None
            group['优先级'] = None
            group['出差批次'] = None  # 用于标识同一单据内的不同出差批次
            group['同一单据的总出差天数'] = None  # 存储同一次出差的总天数
            group['同一单据的疑似自带车总天数'] = None  # 新增：存储同一次出差的疑似自带车总天数
            
            # 获取组内的所有索引
            group_indices = group.index.tolist()
            
            if len(group_indices) == 0:
                return group
                
            # 设置初始常住地（第一条记录的出发地名称）
            initial_residence = group.loc[group_indices[0], '出发地名称']
            
            current_batch = 1  # 当前出差批次
            batch_start_idx = 0  # 当前批次的起始索引
            
            i = 0
            while i < len(group_indices):
                if i == 0:
                    # 第一段行程使用初始常住地
                    current_residence = initial_residence
                else:
                    # 后续段使用当前记录的出发地名称作为新的常住地
                    current_residence = group.loc[group_indices[i], '出发地名称']
                
                # 记录当前段的起始时间
                start_time = group.loc[group_indices[i], '出发时间']
                
                # 设置这条记录的常住地
                group.loc[group_indices[i:], '常住地'] = current_residence
                
                # 在当前位置之后查找第一条目的地等于常住地的记录
                found = False
                for j in range(i, len(group_indices)):
                    if group.loc[group_indices[j], '目的地名称'] == current_residence:
                        end_time = group.loc[group_indices[j], '到达时间']
                        # 计算出差天数
                        days = (end_time - start_time).total_seconds() / (24 * 3600) + 1
                        # 为这一段的所有记录设置相同的出差天数
                        group.loc[group_indices[i:j+1], '出差天数'] = days
                        # 设置优先级
                        group.loc[group_indices[i:j+1], '优先级'] = '一级' if days <= 2 else '二级'
                        # 设置出差批次
                        group.loc[group_indices[i:j+1], '出差批次'] = current_batch
                        
                        # 计算每个人的疑似自带车总天数
                        batch_records = group.loc[group_indices[batch_start_idx:j+1]]
                        for person_id in batch_records['人员编号'].unique():
                            person_mask = (batch_records['人员编号'] == person_id)
                            # 计算该人员使用6002交通工具的次数
                            batch_records['交通工具编号'] = batch_records['交通工具编号'].astype(str)
                            self_driving_count = (batch_records.loc[person_mask, '交通工具编号'] == '6002').sum()
                            
                            # 获取该人员在当前批次中的所有索引
                            person_indices = batch_records.index[person_mask]
                            # 为该人员的所有记录设置其自带车总天数
                            group.loc[person_indices, '同一单据的疑似自带车总天数'] = self_driving_count
                        
                        # 如果返回常住地，说明这是一次完整的出差，开始新的批次
                        current_batch += 1
                        batch_start_idx = j + 1
                        
                        # 移动到下一个起点
                        i = j + 1
                        found = True
                        break
                
                if not found:
                    # 如果没有找到匹配的记录，移动到下一条
                    i += 1
            
            # 在所有批次处理完后，计算每个人的总出差天数
            for person_id in group['人员编号'].unique():
                # 获取该人员的所有记录
                person_records = group[group['人员编号'] == person_id]
                # 按批次分组并求和每个批次的出差天数
                total_days = person_records.groupby('出差批次')['出差天数'].first().sum()
                # 将总天数设置到该人员的所有记录中
                group.loc[group['人员编号'] == person_id, '同一单据的总出差天数'] = total_days
            
            return group
        
        # 应用分组处理
        merged_data = merged_data.groupby(['单据编号', '人员编号']).apply(calculate_travel_days)
        
        # 过滤出差天数为空或负数的数据
        valid_data = merged_data[
            merged_data['出差天数'].notna() & (merged_data['出差天数'] > 0)
        ].copy()
        
        # 确保交通工具编号为字符串型
        valid_data['交通工具编号'] = valid_data['交通工具编号'].astype(str)
        
        # 处理时间格式，只保留年月日
        datetime_columns = ['出发时间', '到达时间']
        for col in datetime_columns:
            if col in valid_data.columns:
                valid_data[col] = valid_data[col].dt.strftime('%Y-%m-%d')
        
        # 获取交通补助金额
        traffic_allowance = travel_allowance[
            (travel_allowance['补助类型名称'] == '交通补助') & 
            (pd.to_numeric(travel_allowance['金额'], errors='coerce') > 0)
        ][['单据编号', '金额']].copy()
        
        # 将金额按单据编号合并到结果中
        valid_data = pd.merge(
            valid_data,
            traffic_allowance,
            on='单据编号',
            how='left'
        )
        
        # 将NaN值填充为0
        valid_data['金额'] = valid_data['金额'].fillna(0)
        valid_data['领取交通补助总天数'] = valid_data['领取交通补助总天数'].fillna(0)
        
        # 重命名金额列为交通补助金额
        valid_data = valid_data.rename(columns={'金额': '交通补助金额'})
        
        # 删除多余的列
        columns_to_drop = [col for col in valid_data.columns if col.endswith('_transport')]
        valid_data = valid_data.drop(columns=columns_to_drop)
        
        # 删除重复值
        valid_data = valid_data.drop_duplicates()
        
        # 过滤掉交通补助金额为0的记录
        final_data = valid_data[pd.to_numeric(valid_data['交通补助金额'], errors='coerce') > 0].copy()
        
        # 筛选问题项：总出差天数减去疑似自带车总天数大于领取交通补助的总天数的记录
        problem_data = final_data[
            (final_data['同一单据的总出差天数'] - final_data['同一单据的疑似自带车总天数']) > 
            final_data['领取交通补助总天数']
        ].copy()

        # 筛选申请事由包含"培训"的记录
        if '申请事由' in problem_data.columns:
            training_data = problem_data[problem_data['申请事由'].str.contains('培训', na=False)].copy()
            print(f"\n培训相关记录数: {len(training_data)}")
            print(f"非培训记录数: {len(problem_data) - len(training_data)}")
        else:
            print("\n警告：数据中没有'申请事由'列，无法筛选培训记录")
            training_data = problem_data
        
        print(f"\n最终记录数（过滤金额为0后）: {len(final_data)}")
        print(f"金额为0而被过滤的记录数: {len(valid_data) - len(final_data)}")
        print(f"问题记录数: {len(problem_data)}")
        
        # 打印优先级统计
        priority_counts = training_data['优先级'].value_counts()
        print("\n培训记录的优先级统计:")
        print(f"一级（小于等于2天）: {priority_counts.get('一级', 0)}条")
        print(f"二级（大于2天）: {priority_counts.get('二级', 0)}条")
        
        # 保存结果
        with pd.ExcelWriter(OUTPUT_FILE, engine='openpyxl', mode='w') as writer:
            # 只保存培训相关的问题记录
            training_data.to_excel(writer, index=False)
            
            # 设置分录编号和辅助编号列为文本格式
            if any(col in training_data.columns for col in ['分录编号', '辅助编号']):
                worksheet = writer.sheets['Sheet1']
                for idx, col in enumerate(training_data.columns):
                    if col in ['分录编号', '辅助编号']:
                        # 设置整列为文本格式
                        col_letter = chr(65 + idx)  # 将列索引转换为Excel列字母
                        worksheet.column_dimensions[col_letter].number_format = '@'
                        # 设置每个单元格为文本格式
                        for row in range(2, len(training_data) + 2):
                            cell = worksheet.cell(row=row, column=idx + 1)
                            cell.number_format = '@'  # 设置单元格格式为文本
                            # 确保单元格的值是字符串格式
                            cell.value = str(cell.value).zfill(4)
        
        return training_data
        
    except Exception as e:
        print(f"分析数据时出错: {str(e)}")
        raise

def main(once = False):
    """主程序入口"""
    try:
        print("正在加载数据...")
        travel_details, transport_expenses, travel_allowance = load_data()
        
        if travel_details.empty or transport_expenses.empty:
            print("错误：输入数据为空")
            return False
            
        print("正在分析数据...")
        result = analyze_travel_data(travel_details, transport_expenses, travel_allowance)
        
        if result.empty:
            print("未找到匹配的数据")
            return False

        if once:
            return result
        
        print(f"\n分析完成。结果已保存至: {OUTPUT_FILE}")
        print(f"问题记录总数: {len(result)}")
        
        # 打印统计信息
        print("\n数据统计:")
        print(f"唯一单据编号数量: {result['单据编号'].nunique()}")
        print(f"唯一人员编号数量: {result['人员编号'].nunique()}")
        print(f"包含出差天数的记录数: {result['出差天数'].notna().sum()}")
        print(f"包含交通补助的记录数: {(result['交通补助金额'] > 0).sum()}")
        
        return True
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        raise

if __name__ == "__main__":
    main() 